package com.sk.skplanet.commute.daofactory;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;

import com.sk.skplanet.commute.vo.BusTblInfo;
import com.sk.skplanet.commute.vo.Data;
import com.sk.skplanet.commute.vo.Point;
import com.sk.skplanet.commute.vo.StopInfo;

public class LocalDaoFactory {
	private static final String FILE_PATH = Environment.getExternalStorageDirectory().getAbsolutePath() + Data.FilePATH
			+ Data.FileNAME;
	private static final String DATABASE_NAME = "mybus.db";
	private static final int DATABASE_VERSION = 1;
	
	private class DatabaseHelper extends SQLiteOpenHelper{

		public DatabaseHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, name, factory, version);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(BusTblInfo.points._CREATE);
			db.execSQL(BusTblInfo.stopInfos._CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS "+BusTblInfo.points._TABLENAME);
			db.execSQL("DROP TABLE IF EXISTS "+BusTblInfo.stopInfos._TABLENAME);
			onCreate(db);
		}
		
	}

	public LocalDaoFactory() {
		if (Data.sqlite == null) Data.sqlite = SQLiteDatabase.openDatabase(FILE_PATH, null,
				SQLiteDatabase.OPEN_READWRITE);
		DatabaseHelper mDBHelper = new DatabaseHelper(null, DATABASE_NAME, null, DATABASE_VERSION);
		if (Data.myBusDB == null) Data.myBusDB = mDBHelper.getWritableDatabase();
	}
	
	public LocalDaoFactory(Context context) {
		if (Data.sqlite == null) Data.sqlite = SQLiteDatabase.openDatabase(FILE_PATH, null,
				SQLiteDatabase.OPEN_READWRITE);
		DatabaseHelper mDBHelper = new DatabaseHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
		if (Data.myBusDB == null) Data.myBusDB = mDBHelper.getWritableDatabase();
	}

	public LocalDaoFactory(int num) {
		if (Data.sqlite == null) Data.sqlite = SQLiteDatabase.openDatabase(Environment.getExternalStorageDirectory()
				.getAbsolutePath() + Data.FilePATH2 + Data.FileNAME, null, SQLiteDatabase.OPEN_READWRITE);
		DatabaseHelper mDBHelper = new DatabaseHelper(null, DATABASE_NAME, null, DATABASE_VERSION);
		if (Data.myBusDB == null) Data.myBusDB = mDBHelper.getWritableDatabase();
	}

	/** 테이블의 Create SQL문을 실행하여 테이블을 생성 */
	public static Cursor selectIDQuery(String data, String table_name) {
		String query = "";
		if (Data.TABLE_STATION.equals(table_name)) query = "select stationId, stationNameKor from station where stationNameKor like '%"
				+ data + "%'";
		else if (Data.TABLE_BUS.equals(table_name)) query = "select busId, busNumberKor, busType from bus where busNumberKor like '%"
				+ data + "%'";
		else if (Data.TABLE_CONFIG.equals(table_name)) query = "select key, value from config where key = '" + data
				+ "'";
		return (Cursor) Data.sqlite.rawQuery(query, null);
	}
	
	public static Cursor selectStationByStopCode(String data) {
		String query = "select stationId, stationNameKor from station where stationId like '" + data + "%'";
		return (Cursor)Data.sqlite.rawQuery(query, null);
	}
	public static Cursor selectBusByBusName(String data) {
//		String query = "select busId, busNumberKor, busType, fromStopKor, toStopKor from bus b join busRunInfo i on b.busNumberKor = i.busNumber where i.type = 'W' and b.busNumberKor like '" + data + "%'";
		String query = "select busId, busNumberKor, busType from bus where busNumberKor like '" + data + "%'";
		return (Cursor)Data.sqlite.rawQuery(query, null);
	}

	public static void updateAuthKey(String AuthKey) {
		//String query = "update config set authKey = ";
		ContentValues arg = new ContentValues();
		arg.put("value", AuthKey);
		Data.sqlite.update("config", arg, "key = ?", new String[] { "authKey" });
	}
	
	public static long insertPoint(String name){
		ContentValues values = new ContentValues();
		values.put(BusTblInfo.points.NAME, name);
		return Data.myBusDB.insert(BusTblInfo.points._TABLENAME, null, values);
	}
	
	public static boolean updatePoint(long id, String name){
		ContentValues values = new ContentValues();
		values.put(BusTblInfo.points.NAME, name);
		return Data.myBusDB.update(BusTblInfo.points._TABLENAME, values, "_id="+id, null) > 0;
	}
	
	public static boolean deletePoint(long id){
		Data.myBusDB.delete(BusTblInfo.stopInfos._TABLENAME, BusTblInfo.stopInfos.POINTID + "=" + id, null);
		return Data.myBusDB.delete(BusTblInfo.points._TABLENAME, "_id="+id, null) > 0;
	}
	
	public static Cursor getPoints(){
		return Data.myBusDB.query(BusTblInfo.points._TABLENAME, null, null, null, null, null, null);
	}
	
	public static Point getPoint(long id){
		Cursor c = Data.myBusDB.query(BusTblInfo.points._TABLENAME, null, 
				"_id="+id, null, null, null, null);
		if(c != null && c.getCount() != 0)
			c.moveToFirst();
		
		return new Point(
				c.getInt(c.getColumnIndex(BusTblInfo.points._ID)), 
				c.getString(c.getColumnIndex(BusTblInfo.points.NAME)), 
				0);
	}
	
	public static long insertStopinfo(String stopId, String stopName, String busId, String busName, String busType, int pointId){
		ContentValues values = new ContentValues();
		values.put(BusTblInfo.stopInfos.STOPID, stopId);
		values.put(BusTblInfo.stopInfos.STOPNAME, stopName);
		values.put(BusTblInfo.stopInfos.BUSID, busId);
		values.put(BusTblInfo.stopInfos.BUSNAME, busName);
		values.put(BusTblInfo.stopInfos.BUSTYPE, busType);
		values.put(BusTblInfo.stopInfos.POINTID, pointId);
		return Data.myBusDB.insert(BusTblInfo.stopInfos._TABLENAME, null, values);
	}
	
	public static boolean deleteStopinfo(long id){
		return Data.myBusDB.delete(BusTblInfo.stopInfos._TABLENAME, "_id="+id, null) > 0;
	}
	
	public static Cursor getStopinfos(){
		return Data.myBusDB.query(BusTblInfo.stopInfos._TABLENAME, null, null, null, null, null, null);
	}
	
	public static StopInfo getStopinfo(long id){
		Cursor c = Data.myBusDB.query(BusTblInfo.stopInfos._TABLENAME, null, 
				"_id="+id, null, null, null, null);
		if(c!=null && c.getCount() != 0)
			c.moveToFirst();
		return new StopInfo(
				c.getInt(c.getColumnIndex(BusTblInfo.stopInfos._ID)), 
				c.getString(c.getColumnIndex(BusTblInfo.stopInfos.STOPID)),
				c.getString(c.getColumnIndex(BusTblInfo.stopInfos.STOPNAME)),
				c.getString(c.getColumnIndex(BusTblInfo.stopInfos.BUSID)),
				c.getString(c.getColumnIndex(BusTblInfo.stopInfos.BUSNAME)),
				c.getString(c.getColumnIndex(BusTblInfo.stopInfos.BUSTYPE)),
				c.getInt(c.getColumnIndex(BusTblInfo.stopInfos.POINTID))
				);
	}
	
	public static List<StopInfo> getStopinfosByPointID(long id){
		Cursor c = Data.myBusDB.query(BusTblInfo.stopInfos._TABLENAME, null, 
				BusTblInfo.stopInfos.POINTID + "=" + id, null, null, null, null);
		if(c!=null && c.getCount() != 0)
			c.moveToFirst();
		
		List<StopInfo> result = new ArrayList<StopInfo>();
		
		while(!c.isAfterLast()) {
			result.add(new StopInfo(
							c.getInt(c.getColumnIndex(BusTblInfo.stopInfos._ID)), 
							c.getString(c.getColumnIndex(BusTblInfo.stopInfos.STOPID)),
							c.getString(c.getColumnIndex(BusTblInfo.stopInfos.STOPNAME)),
							c.getString(c.getColumnIndex(BusTblInfo.stopInfos.BUSID)),
							c.getString(c.getColumnIndex(BusTblInfo.stopInfos.BUSNAME)),
							c.getString(c.getColumnIndex(BusTblInfo.stopInfos.BUSTYPE)),
							c.getInt(c.getColumnIndex(BusTblInfo.stopInfos.POINTID))
				));
			
			c.moveToNext();
		}
		
		return result;
	}
}